package com.tim.dao.Impl;

import com.tim.dao.StallUseDao;
import com.tim.pojo.StallUse;
import com.tim.util.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * 作者：Tim
 * 时间：2021/4/29 11:57
 */
public class StallUseDaoImpl implements StallUseDao {
    @Override
    public int deleteStallUse(int id) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "delete from stalluse where id = ?";
        Object[] args ={id};
        return qr.update(sql,args);
    }

    @Override
    public int addStallUse(StallUse stallUse) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "insert into stalluse (id,carPlot,stallNum,carNum,image,carOwner,tel,useable,price,startTime,endTime,creatorTime) values (?,?,?,?,?,?,?,?,?,?,?,?)";
        Object[] args = {stallUse.getId(),stallUse.getCarPlot(),stallUse.getStallNum(),stallUse.getCarNum(),stallUse.getImage(),stallUse.getCarOwner(),stallUse.getTel(),stallUse.getUseable(),stallUse.getPrice(),stallUse.getStartTime(),stallUse.getEndTime(),stallUse.getCreatorTime()};
        return qr.update(sql,args);
    }

    @Override
    public StallUse selectOne(int id) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select * from stalluse where id = ?";
        Object[] args = {id};
        return qr.query(sql,new BeanHandler<StallUse>(StallUse.class),args);

    }

    @Override
    public int updateStallUse(StallUse stallUse) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql ="update stalluse carPlot=?,stallNum=?,carNum=?,image=?,carOwner=?,tel=?,useable=?,price=?,startTime=?,endTime=?,creatorTime=? where id = ?";
        Object[] args = {stallUse.getCarPlot(),stallUse.getStallNum(),stallUse.getCarNum(),stallUse.getImage(),stallUse.getCarOwner(),stallUse.getTel(),stallUse.getUseable(),stallUse.getPrice(),stallUse.getStartTime(),stallUse.getEndTime(),stallUse.getCreatorTime(),stallUse.getId()};
        return  qr.update(sql,args);
    }

    @Override
    public List<StallUse> searchStallUse(String carNum) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select * from stalluse where carNum like ?";
        Object[] args = {"%"+carNum+"%"};
        return qr.query(sql,new BeanListHandler<StallUse>(StallUse.class),args);
    }

    @Override
    public List<StallUse> limit(Integer currentPage, Integer pageSize, String carNum) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        if(carNum==null){
            String sql ="select * from stalluse limit ?,?";
            Object[] args = {(currentPage-1)*pageSize,pageSize};
            return qr.query(sql,new BeanListHandler<StallUse>(StallUse.class),args);
        }else{
            String sql ="select * from stalluse where carNum = ? limit ?,?";
            Object[] args = {"%"+carNum+"%",(currentPage-1)*pageSize,pageSize};
            return qr.query(sql,new BeanListHandler<StallUse>(StallUse.class),args);
        }
    }

    @Override
    public int count(String carNum) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        if(carNum == null){
            String sql = "select count(*) from stalluse";
            return qr.query(sql,new ScalarHandler<Number>()).intValue();
        }else{
            String sql = "select count(*) from stalluse where carNum like ?";
            return qr.query(sql,new ScalarHandler<Number>(),"%"+carNum+"%").intValue();
        }
    }
}
